<?php
	
	namespace Aspic\Database;
	use Aspic as A;
	
	/**
	* Sql utilities
	* 
	* @Author Mallory Dessaintes <mdessaintes@gmail.com>
	* @version 1.0
	*/
	abstract class AbstractUtil {
        const JOIN_TYPE_LEFT = 'LEFT INNER';
        const JOIN_TYPE_LEFT_OUTER = 'LEFT OUTER';
        const JOIN_TYPE_RIGHT = 'RIGHT INNER';
        const JOIN_TYPE_RIGHT_OUTER = 'RIGHT OUTER';
        
		const NOT_NULL         = 1; // null
		const NOT_EMPTY        = 2; // ''
		const NOT_FALSE        = 4; // false
		const NOT_ZERO         = 8; // '0' OR 0
		const NOT_EMPTY_ARRAY  = 16; // is_array($array) AND count($array) != 0 (so MUST BE AN ARRAY)
		const NO_CONSTRAINT    = 32;
		
		/**
		* @var ressource This is the Database link identifier (if necessary)
		*/
		protected $_dbLink;
		
		/**
		* @param ressource $link_identifier Represent the database ressource (it is use for methods like escape_string)
		*/
		public function __construct($_dbLink = null) {
			$this->_dbLink = $_dbLink;
		}
		
		/**
		* Return escaped $string value surround by quotes
		*/
		public function escape($string) {
			return $this->_dbLink->quote($string);
		}
		
		/**
		* Return a escaped and surround (if necessary) string based on the type of the object
		*/
		public function escapeAndSurround($data) {
			if(is_string($data)) {
				$data = $this->escape($data);
				
				return $data;
			}
			elseif(is_numeric($data)) {
				return $data;
			}
			elseif(is_null($data)) {
				return 'NULL';
			}
			elseif(is_array($data)) {
				return '"Array"';
			}
			elseif(is_bool($data)) {
				return ($data)?1:0;
			}
			elseif(is_object($data)) {
				return '"Object"';
			}
			else {
				return $data;
			}
		}
	
		/**
		* Quotes a value and places into a piece of text at a placeholder.
		* 	Usage : 
		* 		SELECT * FROM user WHERE username = ? AND password = ?
		* 		SELECT * FROM user WHERE (username = {1} AND password = {2}) OR (username = {1} AND password = {3})
		*/
		public function quoteInto($query) {
			$args = func_get_args();
			
			if(count($args) > 1) {
				array_shift($args);
			}
			else {
				$args = array();
			}
			
			return call_user_func(array($this, 'quoteIntoArray'), $query, $args);
		}
		
		public function quoteIntoArray($query, array $args) {
			foreach($args as &$arg) {
				$arg = $this->escapeAndSurround($arg);
			}
			
			$escapedQuery = \Aspic\UString::fromArgsArray($query, $args);
			
			return $escapedQuery;
		}
		
		/**
		* This function help the writing of queries with joins. It makes a string of all the columns you need,
		* prefixed with the joined table alias ("JOIN myTable myTableAlias ON .."). you can also specify a prefix 
		* and a suffix for the alias of the column (if joined table have same columns names)
		* 
		* Example: columnsPrefixer('col1;col2','myTable','myTableRow1_','_suffix') 
		* will return: 
		* myTable.col1 as myTableRow1_col1_suffix, myTable.col2 as myTableRow1_col2_suffix
		* 
		* @param string|array $cols Columns list (array or string delimit by ; like 'col1;col2')
		* @param string $tableAlias The joined table alias ("JOIN myTable myTableAlias ON ..")
		* @param string $aliasPrefix Column's alias prefix (default: same as the tableAlias followed by "_")
		* @param string $aliasSuffix Column's alias suffix
		* 
		*/
		public function columnsPrefixer($cols, $tableAlias, $aliasPrefix = null, $aliasSuffix = '') {
			if(!is_array($cols)) {
				$cols = explode(';',$cols);
			}
			
			if(is_null($aliasPrefix)) {
				$aliasPrefix = $tableAlias.'_';
			}
			
			$colsStrings = array();
			
			foreach($cols as $col) {
				$colsStrings[] = $tableAlias.'.'.$col.' AS "'.$aliasPrefix.$col.$aliasSuffix.'"';
			}
			
			$colsString = implode(', ', $colsStrings);
			
			return $colsString;
		}
		
		/**
		* Filter object to get only the columns of a db table (object could contains columns from a join)
		* It return all item of $values for which key is present in $columns
		*/
		public function getEffectiveFields($values, $columns) {
			$values = A\UArray::toArray($values);
			
			$effFields = array();
			$valuesFields = array_keys($values);
			
			// This is done to see also values that are null for which isset return false
			foreach($columns as $name) {
				if(in_array($name, $valuesFields)) {
					$effFields[$name] = $values[$name];
				}
			}

			return $effFields;
		}
		
		/**
		* Check if field (or an array fields) is a real field of the table
		* 
		* @param string|array $fields Field name of field array to check existence
		*/
		public function isTableField($fields, $columns) {
			if(!is_array($fields)) {
				$fields = array($fields);
			}
			
			foreach($fields as $field) {
				if(!in_array($field, $columns)) {
					return false;
				}
			}
			
			return true;
		}
		
		/** 
		* Get value state in binary (used in getAssociationPairs)
		* ex: 1111 => value is not null, not empty, not false, not empty array and not a zero
		*/
		protected static function getValueState($value) {
			$valueState = 
				static::NO_CONSTRAINT                                                     |
				(static::NOT_NULL        * (int)!is_null($value))                         |
				(static::NOT_EMPTY       * (int)!($value === ''))                         |
				(static::NOT_FALSE       * (int)!($value === false))                      |
				(static::NOT_ZERO        * (int)!($value === '0' OR $value === 0))        |
				(static::NOT_EMPTY_ARRAY * (int)(is_array($value) AND count($value) > 0)) 
			;
			
			return $valueState;
		}
		
		public function getAssociationPairString($name, $sign, $value, $isWhereExpression) {
			$sql = $name.' '.$sign.' '.$this->escapeAndSurround($value);
			
			return $sql;
		}
		
		/**
		* Check if search constraint pass or not
		*/
		protected static function searchConstraintPass($value, $columnName, $constraints) {
			$valueState = static::getValueState($value);
			
			$constraint = A\Util::ifSet($constraints[$columnName], $constraints['*']);
			
			return A\Util::bitsMatch($valueState, $constraint);
		}
		
		/**
		* Return an array containing for each $value : column = "value" if column is a column of the table ($columns arg)
		* 
		* The sign "=" could be changed by replacing pair 'column' => 'value' with 'column' => array('sign', 'value') like:
		* 'pseudo' => array('!=', 'foo')
		* 
		* Sign could be "expr" (expression), and in this case the second row contains the expression (see UString::fromArgs) and 
		* the third row contains the params (in an array). A fourth optionnal param (only when $params['search'] is true) could be true or false 
		* and set if the expr will be add to the sql query or not (ex: for LIKE queries, because all user input is surround with '%userInput%'
		* and then always != from ''
		* 
		* Sign could be "orList" => Second line is an array of the possible values for the column, to retreive the database line.
		* like: array('orList', array('one', 'two', 'three'))
		* 
		* Sign could be "andList" : used to add multiple check on a same column (because array can only contains one time a key) See below for details
		* 
		* @param array $columns Only theses columns names will be accepted
		*/
		public function getAssociationPairs($values, $isWhereExpression, array $params = array()) {
			$values = A\UArray::toArray($values);
			
			$params = A\UArray::mergeRecursiveSimple(array(
				'columns' => null,
				'search' => false,
				'searchFieldsConstraints' => array('*' => static::NOT_NULL | static::NOT_EMPTY), 
			), $params);
			
			$columns = $params['columns'];
			
			if(is_array($columns) AND count($columns)) {
				$values = $this->getEffectiveFields($values, $columns);
			}
			
			$pairs = array();
			
			foreach($values as $columnName => $v) {
				if($isWhereExpression) {
					$sign = (is_array($v)?$v[0]:'=');
//					var_dump($columnName, $v);
					/*
					array(
						'price' => array(
							'expr', 
							'BETWEEN ? AND ?', 
							array($priceMin, $priceMax), 
							(bool)$checkboxSearchForPrice // if true,  expr will be evaluated
						)
					)
					*/
					if($sign == 'expr') { // expression like:   LIKE "%?%" => Will be tested if all params are ok (when search param set to true and searchFieldsConstraints pass)
						$expr = $v[1];
						$args = A\Util::ifSet($v[2], array());

						$evaluate = A\Util::getIfSet($v[3]);

						if($params['search']) {
							if($evaluate OR $evaluate === null) {
								$constraintPass = true;

								foreach($args as $v) {
									if(static::searchConstraintPass($v[1], $columnName, $params['searchFieldsConstraints'])) { // Test not pass
										$constraintPass = false;
										break;
									}
								}

								if($constraintPass) {
									$pairs[] = $columnName.' '.$this->quoteIntoArray($expr, $args);
								}
							}
						}
						if(!$params['search'] OR (isset($constraintPass) AND $constraintPass)) {
							$pairs[] = $columnName.' '.$this->quoteIntoArray($expr, $args);
						}
					}
					else { // There is only one value to check for search Constraint
						$value = (is_array($v)?$v[1]:$v); // We get the value heres

						if(
							!$params['search'] OR
							static::searchConstraintPass($value, $columnName, $params['searchFieldsConstraints'])
						) {
							// orList: 'columnName' => array('orList', array('one', 'two', 'three'))
							if($sign == 'orList') {
								$list = $v[1];

								$where = array();

								foreach($list as $el) {
									$where[] = array($columnName => $el);
								}

								$pairs[] = '('.$this->getWhereString($where, false).')';
							}
							/*
								andList

								Used to add multiple check on a same column (because array can only contains one time a key)
								=> array(
									'columnName' => array(
										'andList', 
										array(
											array(
												'!=', 
												null, 
											), 
											array(
												'>', 
												3, 
											), 
										)
									)
								)
								=> WHERE columnName IS NOT NULL AND columnName > 3
							*/
							elseif($sign == 'andList') {
								$ands = array();

								foreach($v[1] as $value) {
									$sql = $this->getWhereString(array($columnName => $value), false, $params);
									// var_dump($sql);
									// var_dump($params);
									if($sql) {
										$ands[] = $sql;
									}
								}

								$sql = implode(' AND ', $ands);
								$pairs[] = $sql;
							}
							else {
								$pairs[] = $this->getAssociationPairString($columnName, $sign, $value, $isWhereExpression);
							}
						}
					}
				}
				else {
					$pairs[] = $this->getAssociationPairString($columnName, '=', $v, false);
				}
			}
			
			return $pairs;
		}
		
		/**
		* Return a sql where string from a array like array( array('id' => 1, 'pass' => 'abc'), array('id' => 2, 'pass' => 'def'))
		* that will be translated to : WHERE (id = 1 AND pass = "abc") OR  (id = 2 AND pass = "def")
		* 
		* If there is no OR conditions, values could be filled with : array('id' => 1, 'pass' => 'abc') instead of
		* array( array('id' => 1, 'pass' => 'abc') )
		* 
		* $values could also be an instance of WhereGroup classs
		* 
		* @param bool $prefixWithKeyword If true, the sql return will be prefix with "WHERE "
		*/
		public function getWhereString($values, $prefixWithKeyword = true, array $params = array()) {
			$params = array_merge(array(
				'search' => false,
				'searchFieldsConstraints' => array(), 
			), $params);
		
			$sql = (($prefixWithKeyword)?'WHERE ':'');
			
			if(is_array($values) AND count($values)) {
				$or = array();
				
				reset($values);
				
				$first = each($values);
				
				##### PERMET DE NE PAS DEVOIR ECRIRE array(array('field'=>'value')) MAIS JUSTE array('field'=>'value') LORSQU'IL N'Y A PAS DE OR #####
				// An array with changing sign or "expr" could be set like array('abc' => array('!=', 'def'))
				// Key 0 should not be set because only column names are the key of last array
				if(!is_array($first['value']) OR array_key_exists(0, $first['value'])) {
					$values = array($values);
				}
				
				foreach($values as $k => $v) {
					$and = $this->getAssociationPairs($v, true, $params);
					
					if($and) {
						$or[] = implode(' AND ', $and);
					}
				}
				
				if(count($or)) { // Could be empty if $params['search'] set to true and all fields evaluated to false
					$sql .= '('.implode(') OR (', $or).')';
				}
				else {
					$sql = '';
				}
				
			}
			elseif($values instanceof WhereGroup) {
				$sql = $values->getWhereString($this);
			}
			elseif(is_string($values)) {
				$sql .= $values;
			}
			else {
				$sql = '';
			}
			
			return $sql;
		}
		
		/**
		* Return orderBy string
		* 
		* @param array|string|null $orderBy An array containing columns to sort like array('colName' => 'direction', 'colName2')
		* A string like "col1, col2, col3 sortOrder" 
		* 
		* @param array $columns Check if the orders columns are in this array, if not, the result is empty
		* (Work only when $orderBy is an array)
		*/
		public function getOrderByStr($orderBy, $columns = null) {
			$sql = '';
			
			if(is_string($orderBy)) {
				$sql = $orderBy;
			}
			elseif(is_array($orderBy)) {
				foreach ($orderBy as $k => $v) {
					if (is_int($k)) {
						$colName = $v; // array('colName');
						$sortOrder = 'ASC';
					}
					else {
						$colName = $k; // array('colName' => 'direction');
						$sortOrder = strtoupper($v);
					}
					
					if (is_null($columns) OR $this->isTableField($colName, $columns)) {
						if(!static::isOrderByDirection($sortOrder)) {
							$sortOrder = 'ASC';
						}
						
						$orderByStrs[] = $colName.' '.$sortOrder;
					}
				
					$sql = implode(', ', $orderByStrs);
				}
			}
			
			if($sql) {
				$sql = 'ORDER BY '.$sql;
			}
			
			return $sql;
		}
		
		/**
		* Return limitBy string
		* 
		* @param array|int|string|null $limit An array containing respectively the start Offset and the number of lines to retrieve.
		* An integer which is the number of lines to retrieve
		* A string containing the limit string (manual input)
		* 
		* @param int $offset Offset to start from (only tested when $limit is an integer)
		*/
		public function getLimitStr($limit, $offset = 0) {
			if($limit) {
				$str = 'LIMIT ';
			
				if(is_array($limit)) {
					$limit[0] = intval($limit[0]);
					$limit[1] = intval($limit[1]);
				
					$str .= $limit[0].', '.$limit[1];
				}
				elseif(is_int($limit)) {
					$str .= intval($offset).', '.intval($limit);
				}
				else {	
					$str .= $limit;
				}
				
				return $str;
			}
			else {
				return '';
			}
		}
		
        /**
         * 
         * @param array $joinData array like array(Util::JOIN_TYPE_LEFT, 'tableName', 'tableAlias', array('tableAlias.id' => 'mainTableAlias.fkTableName'))
         * => array($joinType, $tableName,  $tableAlias,  $joinCondition) $joinCondition like is a simple "where" array (convert using getWhereString)
		 * @return string SQL Join string
         */
        public function getJoinStr($joinData) {
            list($joinType, $tableName, $tableAlias, $whereData) = $joinData;
            
            $conditionStr = $this->getWhereString($whereData, false);
            
            $sqlFormat = '? ? ? ON ?';
            
            $sql = A\UString::fromArgs($sqlFormat, 
                $joinType, 
                $tableName, 
                $tableAlias, 
                $conditionStr
            );
            
            return $sql;
        }
        
		/**
		* Retrun join "ON" string like "ON user.id = order.userId" from array('user.id' => 'order.userId')
		*/
		public function getJoinOnStr($onData) {
			if(is_array($onData)) {
				$first = each($onData);
				$sql = $first['key'].' = '.$first['value'];
			}
			elseif(is_string($onData)) {
				$sql = $onData;
			}
			else {
				throw new A\Exception('$onData type is not supported');
			}
			
			if($sql) {
				return 'ON '.$sql;
			}
			else {
				return '';
			}
		}
		
		public static function geSqlType($type, $length) {
			$typeName = '';
			
			$type = strtolower($type);
			
			if ($type == 'string') {
				if ($length) {
					$typeName = 'VARCHAR('.$length.')';
				}
				else {
					$typeName = 'TEXT';
				}
			}
			elseif ($type == 'integer') {
				$typeName = 'INTEGER';
			}
			elseif ($type == 'boolean') {
				$typeName = 'TINYINT';
			}
			elseif ($type == 'float' OR $type == 'double') {
				$typeName = 'DOUBLE';
			}
			
			return $typeName;
		}

		public static function isOrderByDirection($dirStr) {
			$directions = array('asc', 'desc');
			
			$strLower = strtolower($dirStr);
			
			return in_array($strLower, $directions);
		}
		
		/**
		 *
		 * @param array $col A col getted with getCol() method of the dataModel
		 * @return Return an sql column definition from a TableDataModel col
		 */
		public function getColDefinition($col) {
			$sqlType = A\Util::ifSet($col['sqlType'], null);
			$length = A\Util::ifSet($col['typeLength'], null);

			if ($sqlType == null) {
				$typeStr = $this->geSqlType($col['type'], $length);
			}
			else {
				$typeStr = $col['sqlType'];

				if ($col['typeLength']) {
					$typeStr .= '('.$col['typeLength'].')';
				}
			}

			$notNullStr = '';

			if ($col['null'] == 'NO') {
				$notNullStr = ' NOT NULL';
				
				if ($col['defaultValue'] !== null) {
					$notNullStr .= ' DEFAULT '.$this->escapeAndSurround($col['defaultValue']);
				}
			}
			
			$extraStr = '';
			
			if ($col['extra'] == 'auto_increment') {
				$extraStr = ' AUTO_INCREMENT';
			}
			
			$line = '`'.$col['name'].'` '.$typeStr.$notNullStr.$extraStr;
			
			return $line;
		}
		
		/**
		 * Return the php type from sql type given
		 * @param type $sqlType
		 * @return array The php type and the length
		 */
		public function getColPhpType($sqlType) {
			if (strpos($sqlType, '(')) {
				// Last (.*) is for unsigned that is ignored
				$parsed = preg_replace('#(.+)\((.+)\)(.*)#', '$1:$2', $sqlType);
				list($sqlType, $length) = explode(':', $parsed);
			}
			else {
				$length = null;
			}
			
			$conv = array(
				'VARCHAR' => 'string', 
				'TEXT' => 'string', 
				'DATE' => 'string', 
				'DATETIME' => 'string', 
				'INT' => 'integer', 
				'DOUBLE' => 'double', 
				'TINYINT' => 'boolean', 
				'BOOLEAN' => 'boolean', 
			);
			
			$sqlType = strtoupper($sqlType);
			
			if (isset($conv[$sqlType])) {
				$phpType = $conv[$sqlType];
			}
			else {
				$phpType = 'string';
			}
			
			return array($phpType, $length);
		}
		
		/**
		 * Return the sql type and length from sql type definition
		 * @param type $sqlTypeDef
		 */
		public function parseSqlType($sqlType) {
			if (strpos($sqlType, '(')) {
				$parsed = preg_replace('#(.+)\((.+)\)(.*)#', '$1:$2', $sqlType);
				$ret = explode(':', $parsed);
			}
			else {
				$ret = array($sqlType, null);
			}
			
			return $ret;
		}
		
		/**
		 * Convert a given object to array to be used with database
		 * @param array|object $row
		 */
		public function toArray($row) {
			// Using get storable values for Row classes
			if ($row instanceof A\Model\Row AND $row->hasDataModel()) {
				$vars = array();
				$dataModel = $row->getDataModel();

				foreach ($dataModel->getCols() as $col) {
					$colValue = $row->getStorableValue($col['name']);

					$vars[$col['name']] = $colValue;
				}

				return $vars;
			}
			else {
				return A\UArray::toArray($row);
			}
		}
		
	}
	
?>